Model Kit Sales Analysis

1. Discovery

This article is to analyze the sales data of a model kit provider. Their product line consists of model ships, planes, cars and military ground vehicles. I will access the data by creating a connection to the database.

As the sales data including the information of customers, orders, products, product lines and the company employees, I will answer below business problems in this article, so that the management could better understand the company’s business status.

1. How is the sales of each product line?

2. What’s the ordered quantity and stock quantity of each product?

3. As we have regional managers, what’s the sales of the employees under each manager?

I will use advanced SQL functions such as subquery, OLAP, window function to answer.

2. Data Preparation

When working with databases, we should always use packages that are DBI-compliant.

The odbc package provides a standard way for us to connect to any database as long as we have an ODBC driver installed. The odbc package is DBI-compliant, and is recommended for ODBC connections in this project.

The plotly package is loaded for data visualization.

2.1 Connect to the Database

# Load the DBI and odbc packages
library(DBI)
library(odbc)

# Verify that odbc recognizes the installed drivers using odbcListDrivers()
sort(unique(odbcListDrivers()[[1]]))

[1] "ODBC Driver 17 for SQL Server"

# check the available driver
odbcListDrivers(
  keep = getOption("odbc.drivers_keep"),
  filter = getOption("odbc.drivers_filter")
)

Only ODBC Driver 17 for SQL Server is available and we will use it for our connection.

# Pass the server address, database name, and the credentials to be used to the connection
con <- DBI::dbConnect(odbc(),
                 Driver = "ODBC Driver 17 for SQL Server",
                 Server = "aseaspdghrtrain.database.windows.net",
                 Database = "asdb-eas-pd-ghrtrain01",
                 UID = "RZ15912",  # use your own userID
                 PWD = "D7!zUi*#", # use your own password
                 PORT = 1433)

# Check the connection
con

<OdbcConnection> RZ15912@aseaspdghrtrain
  Database: asdb-eas-pd-ghrtrain01
  Microsoft SQL Server Version: 12.00.2000

The connection is set up and we can specify the connection for all sql chunks in the following.

# Set up the connection for all sql chunks
knitr::opts_chunk$set(connection = "con")

Now test with a simply query and save the result to a variable.

-- Select all from offices table
SELECT * FROM offices

# Display the returned result
mydata

That worked and we can create the entity relationship diagram (ERD).

2.2 ERD

By checking all the tables in the database, I created below ERD by Lucidchart. The primary key of each table is highlighed in Red. The data type of each column is listed as well. For table orderDetails, it has composite primary keys orderNumber and productCode.

3. Query for the Business Problems

3.1 The sales of each product line

SELECT 
    p.productLine,
    SUM(o.quantityOrdered * o.priceEach) AS totalSales,
    Round (SUM(o.quantityOrdered * o.priceEach) / (SELECT SUM(quantityOrdered * priceEach) FROM orderdetails), 3) AS salesPct,
    SUM(o.quantityOrdered) AS totalQuantity
FROM orderdetails AS o
LEFT JOIN products AS p
ON o.productCode = p.productCode
Group BY p.productLine
ORDER BY 2 DESC;

top_productLine

This table is what we want and we can proceed the data visualization for presentation.

# Plot the total sales percentage of different product lines
fig1 <- plot_ly(data = top_productLine, labels = ~productLine, values = ~totalSales, 
                type = 'pie',
                text = ~totalSales,
                textposition = 'inside',
                textinfo = 'label+percent',
                insidetextfont = list(color = '#FFFFFF'),
                hovertemplate = "%{label}<br>Sales: %{text:,.0f}<br>Percentage: %{percent}<extra></extra>",
                marker = list(colors = brewer.pal(n = 7, name = "Paired"),
                              line = list(color = '#FFFFFF', width = 1)),
                showlegend = FALSE)

fig1 <- fig1 %>% layout(title = 'Total Sales Percentage by Different Product Lines',
         xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))

fig1

Beside, we can plot the ordered quantity of each product line.

# Plot the ordered quantity of different product lines
fig2 <- plot_ly(data = top_productLine, y = ~reorder(productLine, totalQuantity), 
                x = ~totalQuantity,
                type = "bar", 
                orientation = "h",
                text = ~totalQuantity,
                texttemplate = "%{x:,}",
                textposition = "auto",
                hovertemplate = "%{label}<br>Ordered Qty: %{text:,}<extra></extra>",
                width = 700,
                height = 400)

fig2 <- fig2 %>% layout(title = "Total Quantity Ordered by Different Product Lines",
                        yaxis = list(title = ""),
                        xaxis = list(title = "", showgrid = FALSE, showticklabels = FALSE))

fig2

Classic cars is the biggest product line by sales and quantity sold, accounting for about 40% of total sales. Vintage cars is the second biggest. Compared to other product lines, Trains is the much smaller product line, only accounting for about 2% of total sales.

3.2 The ordered and stock quantity of each product

We can use below query to generate the quantity sold and in stock for each product.

-- New query with ROLLUP clause, without using JOIN
SELECT 
    pl.productLine,
    p.productName,
    SUM(od.quantityOrdered) AS salesQuantity,
    AVG(p.quantityInStock) AS stockQuantity
FROM 
    productlines AS pl, 
    products AS p, 
    orderdetails AS od
WHERE p.productLine = pl.productLine
AND p.productCode = od.productCode
GROUP BY pl.productLine, p.productName -- Use grouping function
ORDER BY 1, 3 DESC;

top_products

We can see that the company has a catalogue of 109 products. Now let’s make a scatter plot to visualize the table, with a filter function for different procut lines.

# plot the product sales and stock
fig3 <- plot_ly(data = top_products, x = ~salesQuantity, y = ~stockQuantity, 
               color = ~productLine, colors = "Paired",
               type = "scatter",
               mode = "markers",
               text = ~productName,
               hovertemplate = "%{text}<br>Order Qty: %{x}<br>Stock Qty: %{y}<extra></extra>",
               width = 750,
               height = 450,
               # add a filter
               transforms = list(
                                list(
                                  type = 'filter',
                                  target = ~productLine,
                                  operation = '=',
                                  value = unique(top_products$productLine))
               ))
# add dropdown list for the filter
fig3 <- fig3 %>% 
            layout(
              title = "Sales and Stock of Products",
              xaxis = list(title = "Sales Quantity"),
              yaxis = list(title = "Stock Quantity"),
              updatemenus = list(
              list(
                type = "dropdown",
                active = 0,
                yanchor = "bottom",
                buttons = list(
                  list(method = "restyle",
                       args = list("transforms[0].value", unique(top_products$productLine)),
                       label = "All"),
                  list(method = "restyle",
                       args = list("transforms[0].value", unique(top_products$productLine)[1]),
                       label = unique(top_products$productLine)[1]),
                  list(method = "restyle",
                       args = list("transforms[0].value", unique(top_products$productLine)[2]),
                       label = unique(top_products$productLine)[2]),
                  list(method = "restyle",
                       args = list("transforms[0].value", unique(top_products$productLine)[3]),
                       label = unique(top_products$productLine)[3]),
                  list(method = "restyle",
                       args = list("transforms[0].value", unique(top_products$productLine)[4]),
                       label = unique(top_products$productLine)[4]),
                  list(method = "restyle",
                       args = list("transforms[0].value", unique(top_products$productLine)[5]),
                       label = unique(top_products$productLine)[5]),
                  list(method = "restyle",
                       args = list("transforms[0].value", unique(top_products$productLine)[6]),
                       label = unique(top_products$productLine)[6]),
                  list(method = "restyle",
                       args = list("transforms[0].value", unique(top_products$productLine)[7]),
                       label = unique(top_products$productLine)[7])
              
                                )))
      )

fig3

We see an outlier! By checking the hovertext, we can know it is 1992 Ferrari 360 Spider Red. It is so popular as only this classic car has an ordered quantity over 1800, while all of the other products are sold with a number between 767 and 1111. Besides, we don’t have a product with very low ordered quantity.

By the filter, we can see that the Train product line only has 3 products. This is the contributing factor to its low sales.

In this plot, we can find the products with low stock (less than 200), they are:

1. 1968 Mustang
2. 1928 Ford Phaeton Deluxe
3. 1997 BWM F650 ST
4. 1960 BSA Gold Star DBD34

3.2 The sales of each sales manager and their subordinates

In employees table, each sales representative has a direct line manager to report to. I will create an SQL View to show the list of each manager and their subordinates.

CREATE VIEW supSub_rz AS
    (
    SELECT
        e2.employeeNumber AS managerID,
        CONCAT(e2.firstName, ' ', e2.lastName) AS managerName,
        e2.jobTitle,
        e1.employeeNumber AS subordinateID,
        CONCAT(e1.firstName, ' ', e1.lastName) AS subordinateName
    FROM employees AS e1, employees AS e2
    WHERE e1.reportsTo = e2.employeeNumber
    ORDER BY 1 OFFSET 0 ROWS
    );

SELECT * FROM supSub_rz

That’s it! The company has 22 employees in total, 1 President, 1 VP Sales. 3 Regional Sales Manager and 1 Sales Rep directly report to the VP Sales. The four guys also have their subordinates reporting to them.

Now let’s write a query with OLAP function ROLLUP to return the sales number of each manager and their subordinates.

SELECT
    s.managerName,
    s.subordinateName,
    SUM(od.quantityOrdered * priceEach) AS sales
FROM supSub_rz AS s, customers AS c, orders AS o, orderdetails AS od
WHERE s.subordinateID= c.salesRepEmployeeNumber
AND c.customerNumber = o.customerNumber
AND o.orderNumber = od.orderNumber
GROUP BY ROLLUP(s.managerName, s.subordinateName)

employee_sales

To have a clearer view for the management, we can add another column to show the sales rank under each sales manager, with window function RANK. According to the job title, we know that each sales manager is responsible for a region, such as NA, EMEA, APAC.

SELECT
    s.managerName,
    s.jobTitle,
    s.subordinateName,
    SUM(od.quantityOrdered * priceEach) AS sales,
    RANK() OVER (PARTITION BY managerName ORDER BY SUM(od.quantityOrdered * priceEach) DESC) AS salesRank
FROM supSub_rz AS s, customers AS c, orders AS o, orderdetails AS od
WHERE s.subordinateID= c.salesRepEmployeeNumber
AND c.customerNumber = o.customerNumber
AND o.orderNumber = od.orderNumber
GROUP BY s.managerName, s.jobTitle, s.subordinateName

employee_rank

# Plot the sales of different sales reps
fig4 <- plot_ly(data = employee_rank, y = ~reorder(subordinateName, sales),
                x = ~sales,
                color = ~managerName,
                type = "bar", 
                orientation = "h",
                text = ~salesRank,
                texttemplate = "%{text}",
                textposition = "auto",
                hovertemplate = "%{label}<br>Sales: %{x:,}<br>Region Rank: %{text}<extra></extra>",
                width = 700,
                height = 400)

fig4 <- fig4 %>% layout(title = "Employee Sales of Different Managers",
                        yaxis = list(title = ""),
                        xaxis = list(title = "", showgrid = TRUE, showticklabels = TRUE),
                        legend = list(x = 0.7, y = 0.2, 
                                      title = list(text = "<b> Line Manager </b>")))

fig4

In this plot, we can clearly see the sales of each sales rep. The sales of each sales rep vary a lot, in the range between 0.3M to over 1.2M. 4 out of the top 5 sales reps are from Gerard’s team (EMEA Region), the other 1 is from Anthony’s team (NA Region). The sales reps from William’s team (APAC Region) has median sales number, while Mary, the company’s VP Sales has Mami Nishi, who directly report to her.

4. Conclusion

From the above analysis, we can conclude that:

1. Classic cars and Vintage cars are the company’s biggest product lines as both have much more sales than others. Trains is the smallest one, largely because of the low number of available products. The company should keep maintaining the good sales of car products. Developing more products in Trains can probably increase the sales of this product line.

2. The 1992 Ferrari 360 Spider Red is the company’s flagship product. The company can make further analysis on the success of this product. The experience and marketing strategy is possible to be applied to other products. The company should pay attention to the 4 low-stock products, stock replenishment is necessary in case they cannot fulfill orders in the near future.

3. The company’s top sales reps are almost for the EMEA and NA region. But some sales with the lowest sales number are also in the two regions. The company can further analyze the sales detail of each sales rep to better understand their performance.